﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.IO;
using System.Data;
using NewsMine.Utilities;

namespace NewsMine.Storage.SQLite
{
    public class SQLiteKeyValueHelper
    {
        string filePath = null;
        string connectionString = null;
        string readOnlyConnectionString = null;

        string folderPath = string.Empty;
        //SQLiteKeyValueHelper recentKVHelper = null;
        object syncObject = new object();


        int pandingItemsAllowedMaxValue = 500;

        object collectionLock = new object();

        Dictionary<string, byte[]> pendingSetOperationsDictionary = new Dictionary<string, byte[]>();
        List<string> pendingRemovalKeys = new List<string>();


        Dictionary<string, bool> frequentKeys = new Dictionary<string, bool>();

        Dictionary<string, bool> FrequentKeys
        {
            get
            {
                lock (collectionLock)
                {
                    return frequentKeys;
                }
            }
        }

        public SQLiteKeyValueHelper(string sqliteFilePath)
        {
            folderPath = Path.GetDirectoryName(sqliteFilePath);

            string recentKVStoreFolder = Path.Combine(folderPath, "RecentlyAdded");

            using (FolderSyncLocker folderLocker = new FolderSyncLocker(folderPath))
            {
                lock (syncObject)
                {
                    filePath = sqliteFilePath;
                    connectionString = string.Format("Data Source={0};Version=3;Pooling=True;Max Pool Size=100;UTF8Encoding=True", filePath);

                    readOnlyConnectionString = string.Format("Data Source={0};Version=3;Pooling=True;Max Pool Size=100;Read Only=True;UTF8Encoding=True", filePath);

                    //if (Directory.Exists(recentKVStoreFolder) == false)
                    //{
                    //    Directory.CreateDirectory(recentKVStoreFolder);
                    //}

                    //recentKVHelper = new SQLiteKeyValueHelper(recentKVStoreFolder);

                    //if the file is not there, just create one with table schema.

                    DoReadFromPersistedPendingStuffFromDiskFile();

                    AddToFrequentKeys(pendingSetOperationsDictionary.Keys.ToList());

                    

                    if (File.Exists(filePath) == false)
                    {
                        var connection = new SQLiteConnection(connectionString);

                        try
                        {
                            connection.Open();

                            using (var command = new SQLiteCommand("CREATE TABLE KeyValue(KEY VARCHAR, DATA BLOB)", connection))
                            {
                                command.ExecuteNonQuery();
                            }

                            using (var command = new SQLiteCommand("CREATE UNIQUE INDEX [keyIndex] ON [KeyValue]([KEY])", connection))
                            {
                                command.ExecuteNonQuery();
                            }

                        }
                        catch (Exception e)
                        {
                            throw;
                        }
                        finally
                        {
                            connection.Close();
                        }
                    }
                }
            }

            DoPendingBulkOperations();
        }

        public void DeleteByKey(string key)
        {
            lock (collectionLock)
            {
                pendingRemovalKeys.Add(key);

                if (pendingSetOperationsDictionary.Keys.Contains(key))
                {
                    pendingSetOperationsDictionary.Remove(key);
                    RemoveFromFrequentKeys(key);

                }

                MakeSurePendingSetOperationsAndRemoveOperationsArePersisted();

                return;
            }

            using (FolderSyncLocker folderLocker = new FolderSyncLocker(folderPath))
            {
                lock (syncObject)
                {
                    var connection = new SQLiteConnection(connectionString);

                    try
                    {
                        connection.Open();

                        using (var command = new SQLiteCommand("delete from KeyValue where Key = @key", connection))
                        {
                            command.Parameters.Add("@key", DbType.String, key.Length).Value = key;

                            command.ExecuteNonQuery();
                        }
                    }
                    catch (Exception e)
                    {
                        throw;
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
        }

        public void Set(string key, byte[] data)
        {
            lock (collectionLock)
            {
                if (pendingRemovalKeys.Contains(key))
                    pendingRemovalKeys.Remove(key);

                if (pendingSetOperationsDictionary.Keys.Contains(key))
                {
                    pendingSetOperationsDictionary[key] = data;
                }
                else
                {
                    pendingSetOperationsDictionary.Add(key, data);
                }

                MakeSurePendingSetOperationsAndRemoveOperationsArePersisted();

                return;
            }


            string constantIdentifier = DateTime.Now.Ticks.ToString() + "_sqlite_setmethod ";

            using (FolderSyncLocker folderLocker = new FolderSyncLocker(folderPath))
            {
                lock (syncObject)
                {
                    TempLogger.LogCustom("sqlitePerformance.txt", " start of the " + constantIdentifier);

                    var connection = new SQLiteConnection(connectionString);

                    try
                    {
                        connection.Open();

                        bool isExists = false;

                        using (var command = new SQLiteCommand("select count(rowid)  from KeyValue where Key = @key", connection))
                        {

                            command.Parameters.Add("@key", DbType.String, key.Length).Value = key;
                            int count = Convert.ToInt32(command.ExecuteScalar());

                            if (count > 0)
                                isExists = true;
                        }

                        if (isExists == true)
                        {
                            using (var command = new SQLiteCommand("update KeyValue set DATA=@data where Key = @key", connection))
                            {
                                command.Parameters.Add("@key", DbType.String, key.Length).Value = key;
                                if (data != null)
                                    command.Parameters.Add("@data", DbType.Binary, data.Length).Value = data;
                                else
                                    command.Parameters.Add("@data", DbType.Binary, 0).Value = null;

                                command.ExecuteNonQuery();
                            }
                        }
                        else
                        {
                            using (var command = new SQLiteCommand("INSERT INTO KeyValue (KEY,DATA) VALUES (@key,@data)", connection))
                            {
                                command.Parameters.Add("@key", DbType.String, key.Length).Value = key;
                                if (data != null)
                                    command.Parameters.Add("@data", DbType.Binary, data.Length).Value = data;
                                else
                                    command.Parameters.Add("@data", DbType.Binary, 0).Value = null;

                                command.ExecuteNonQuery();
                            }
                        }
                    }
                    catch (SQLiteException ex)
                    {
                        throw;
                    }
                    finally
                    {
                        connection.Close();

                        TempLogger.LogCustom("sqlitePerformance.txt", " End of the " + constantIdentifier);
                    }
                }
            }
        }

        public void MakeSurePendingSetOperationsAndRemoveOperationsArePersisted(bool byForce=false )
        {
            if (pendingSetOperationsDictionary.Count >= pandingItemsAllowedMaxValue || byForce==true)
            {
                DoPersistPendingStuffToDiskFile();

                DoPendingBulkOperations();
            }
            else
                DoPersistPendingStuffToDiskFile();

        }

        private void DoPersistPendingStuffToDiskFile()
        {
            FileExetensions.SerializeObjectToFile(Path.Combine(folderPath, "Removables.db"), pendingRemovalKeys);
            FileExetensions.SerializeObjectToFile(Path.Combine(folderPath, "Settables.db"), pendingSetOperationsDictionary);
        }

        private void DoReadFromPersistedPendingStuffFromDiskFile()
        {
            string removablesFile = Path.Combine(folderPath, "Removables.db");
            if (File.Exists(removablesFile))
                pendingRemovalKeys = (List<string>)FileExetensions.DeserializeFromFile(removablesFile);

            string settablesFile = Path.Combine(folderPath, "Settables.db");
            if (File.Exists(settablesFile))
                pendingSetOperationsDictionary = (Dictionary<string, byte[]>)FileExetensions.DeserializeFromFile(settablesFile);

        }


        private void DoPendingBulkOperations()
        {
            if (pendingRemovalKeys.Count == 0 && pendingSetOperationsDictionary.Count == 0)
                return;

            using (FolderSyncLocker folderLocker = new FolderSyncLocker(folderPath))
            {
                lock (syncObject)
                {
                    DateTime startTime = DateTime.UtcNow;

                    var connection = new SQLiteConnection(connectionString);

                    try
                    {
                        connection.Open();

                        using (SQLiteTransaction tran = connection.BeginTransaction())
                        {

                            #region Remove Operations

                            foreach (string key in pendingRemovalKeys)
                            {
                                using (var command = new SQLiteCommand("delete from KeyValue where Key = @key", connection))
                                {
                                    command.Parameters.Add("@key", DbType.String, key.Length).Value = key;

                                    command.ExecuteNonQuery();
                                }
                            }

                            #endregion

                            #region Set operations

                            foreach (var key in pendingSetOperationsDictionary.Keys)
                            {
                                var data = pendingSetOperationsDictionary[key];

                                bool isExists = false;

                                using (var command = new SQLiteCommand("select count(rowid)  from KeyValue where Key = @key", connection))
                                {
                                   

                                    command.Parameters.Add("@key", DbType.String, key.Length).Value = key;
                                    int count = Convert.ToInt32(command.ExecuteScalar());

                                    if (count > 0)
                                        isExists = true;
                                }

                                if (isExists == true)
                                {
                                    using (var command = new SQLiteCommand("update KeyValue set DATA=@data where Key = @key", connection))
                                    {
                                        command.Parameters.Add("@key", DbType.String, key.Length).Value = key;
                                        if (data != null)
                                            command.Parameters.Add("@data", DbType.Binary, data.Length).Value = data;
                                        else
                                            command.Parameters.Add("@data", DbType.Binary, 0).Value = null;

                                        command.ExecuteNonQuery();
                                    }
                                }
                                else
                                {
                                    using (var command = new SQLiteCommand("INSERT INTO KeyValue (KEY,DATA) VALUES (@key,@data)", connection))
                                    {
                                        command.Parameters.Add("@key", DbType.String, key.Length).Value = key;
                                        if (data != null)
                                            command.Parameters.Add("@data", DbType.Binary, data.Length).Value = data;
                                        else
                                            command.Parameters.Add("@data", DbType.Binary, 0).Value = null;

                                        command.ExecuteNonQuery();
                                    }
                                }
                            }

                            #endregion


                            tran.Commit();

                            File.Delete(Path.Combine(folderPath, "Removables.db"));
                            File.Delete(Path.Combine(folderPath, "Settables.db"));

                            AddToFrequentKeys(pendingSetOperationsDictionary.Keys.ToList());

                            pendingRemovalKeys.Clear();
                            pendingSetOperationsDictionary.Clear();



                        }
                    }
                    catch (SQLiteException ex)
                    {
                        throw;
                    }
                    finally
                    {
                        connection.Close();

                        DateTime endTime = DateTime.UtcNow;

                        TempLogger.LogCustom("sqlitePerformance.txt", "Bulk Operation Time taken..." + (endTime - startTime).TotalMilliseconds.ToString() + " Milliseconds.");
                    }
                }
            }
        }

        private void AddToFrequentKeys(List<string> keys)
        {
            if (keys != null)
            {
                foreach (string key in keys)
                {
                    AddToFrequentKeys(key);
                }
            }
        }

        private void AddToFrequentKeys(string key)
        {
            if (!FrequentKeys.ContainsKey(key))
                FrequentKeys.Add(key, true);

        }

        private void RemoveFromFrequentKeys(string key)
        {
            if (FrequentKeys.ContainsKey(key))
            {
                FrequentKeys.Remove(key);
            }
        }

        public byte[] GetEvenItIsInUnsavedItemFromSerializedFile(string key)
        {
            using (FolderSyncLocker folderLocker = new FolderSyncLocker(folderPath))
            {

                string removablesFile = Path.Combine(folderPath, "Removables.db");
                if (File.Exists(removablesFile))
                {
                    var pendingRemovalKeysTemp = (List<string>)FileExetensions.DeserializeFromFile(removablesFile);

                    if (pendingRemovalKeysTemp.Contains(key))
                        return null;
                }

                string settablesFile = Path.Combine(folderPath, "Settables.db");
                if (File.Exists(settablesFile))
                {
                    var pendingSetOperationsDictionaryTemp = (Dictionary<string, byte[]>)FileExetensions.DeserializeFromFile(settablesFile);

                    if (pendingSetOperationsDictionaryTemp.Keys.Contains(key))
                    {
                        return pendingSetOperationsDictionaryTemp[key];
                    }
                }

                return null;
            }
        }

        public byte[] Get(string key)
        {
            key = key.Trim();

            lock (collectionLock)
            {
                if (pendingRemovalKeys.Contains(key))
                    return null;
                else
                    if (pendingSetOperationsDictionary.Keys.Contains(key))
                    {
                        AddToFrequentKeys(key);
                        return pendingSetOperationsDictionary[key];
                    }
            }

            using (FolderSyncLocker folderLocker = new FolderSyncLocker(folderPath))
            {
                lock (syncObject)
                {

                    var connection = new SQLiteConnection(connectionString);

                    try
                    {
                        using (var command = new SQLiteCommand("SELECT DATA FROM KeyValue WHERE KEY=@key", connection))
                        {
                            DateTime startTime = DateTime.UtcNow;
                            connection.Open();
                            command.Parameters.Add("@key", DbType.String, key.Length).Value = key;
                            byte[] buffer = null;
                            using (var reader = command.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    buffer = GetBytes(reader);
                                    AddToFrequentKeys(key);
                                }
                            }

                            DateTime endTime = DateTime.UtcNow;

                            TempLogger.LogCustom("sqlitePerformance.txt", "Get Operation Time taken..." + (endTime - startTime).TotalMilliseconds.ToString() + " Milliseconds.");


                            return buffer;
                        }
                    }
                    catch (Exception e)
                    {
                        throw;
                    }
                    finally
                    {
                        connection.Close();
                    }


                }
            }
        }

        public List<byte[]> GetAll()
        {
            List<byte[]> dataInList = new List<byte[]>();

            lock (collectionLock)
            {
                dataInList.AddRange(pendingSetOperationsDictionary.Values.ToList());
            }

            using (FolderSyncLocker folderLocker = new FolderSyncLocker(folderPath))
            {
                lock (syncObject)
                {
                    var connection = new SQLiteConnection(connectionString);
                    try
                    {
                        using (var command = new SQLiteCommand("SELECT DATA FROM KeyValue", connection))
                        {
                            connection.Open();
                            byte[] buffer = null;
                            using (var reader = command.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    buffer = null;
                                    buffer = GetBytes(reader);

                                    if (buffer != null)
                                        dataInList.Add(buffer);
                                }
                            }

                        }
                    }
                    catch (Exception e)
                    {
                        throw;
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
            return dataInList;

        }

        public bool IsExists(string key)
        {
            lock (collectionLock)
            {
                if (pendingRemovalKeys.Contains(key))
                    return false;

                if (frequentKeys.ContainsKey(key))
                {
                    return true;
                }

                if (pendingSetOperationsDictionary.Keys.Contains(key))
                {
                    AddToFrequentKeys(key);

                    return true;
                }
            }

            bool isExists = false;
            using (FolderSyncLocker folderLocker = new FolderSyncLocker(folderPath))
            {
                lock (syncObject)
                {
                    var connection = new SQLiteConnection(connectionString);
                    try
                    {
                        using (var command = new SQLiteCommand("select count(rowid)  from KeyValue where Key = @key", connection))
                        {
                            connection.Open();

                            command.Parameters.Add("@key", DbType.String, key.Length).Value = key;
                            int count = Convert.ToInt32(command.ExecuteScalar());

                            if (count > 0)
                                isExists = true;

                            AddToFrequentKeys(key);

                        }
                    }
                    catch (Exception e)
                    {
                        throw;
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
            return isExists;

        }

        public int GetCount()
        {
            using (FolderSyncLocker folderLocker = new FolderSyncLocker(folderPath))
            {
                lock (syncObject)
                {
                    var connection = new SQLiteConnection(connectionString);

                    try
                    {
                        connection.Open();

                        using (var command = new SQLiteCommand("select count(rowid)  from KeyValue", connection))
                        {

                            int count = Convert.ToInt32(command.ExecuteScalar());

                            return count + pendingSetOperationsDictionary.Count;
                        }
                    }
                    catch (Exception e)
                    {
                        throw;
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
        }

        public bool AreExists(string[] keys)
        {
            throw new NotImplementedException();
        }

        #region Helper Methods

        static byte[] GetBytes(SQLiteDataReader reader)
        {
            const int CHUNK_SIZE = 2 * 1024;
            byte[] buffer = new byte[CHUNK_SIZE];
            long bytesRead;
            long fieldOffset = 0;
            using (MemoryStream stream = new MemoryStream())
            {
                while ((bytesRead = reader.GetBytes(0, fieldOffset, buffer, 0, buffer.Length)) > 0)
                {
                    stream.Write(buffer, 0, (int)bytesRead);
                    fieldOffset += bytesRead;
                }
                return stream.ToArray();
            }
        }



        #endregion

    }
}